import os

os.chdir(r'D:\A_Document\A_Microsoft_VS_Code\Python\Work_8')

import pandas as pd

# 读取user文件
df_user = pd.read_csv('users.csv')

# 读取item文件
df_item = pd.read_csv('items.csv')
df_item['rating'] = 0 # 新增rating列，初始值为0

# 读取behavior文件, behavior文件中只包含user_id、item_id和action三列, action包括buy、view、rate
df_behavior = pd.read_csv('user_behavior.csv')
df_behavior['rating'] = df_behavior['rating'].fillna(0) # rating列如果None，则赋值为0

# 将df_item的4个列赋给df
df = df_item[['item_id', 'name', 'brand', 'price']]

# 统计每个商品被购买的次数
df1 = df_behavior[df_behavior['action'] == 'buy'].groupby('item_id').size().reset_index()
df1.columns = ['item_id', 'sales'] # 将列名改为sales
df['sales'] = df1['sales'] # 将df1的sales列赋给df的sales列
df['sales'] = df['sales'].fillna(0) # 将df的sales列中的NaN值替换为0

# 统计每个商品被浏览的次数
df2 = df_behavior[df_behavior['action'] == 'view'].groupby('item_id').size().reset_index()
df2.columns = ['item_id', 'view_count'] # 将列名改为view_count
df['view_count'] = df2['view_count'] # 将df2的view_count列赋给df的view_count列
df['view_count'] = df['view_count'] + df['sales'] # 浏览次数要加上销售数，因为销售数也是浏览数
df['view_count'] = df['view_count'].fillna(0) # 将df的view_count列中的NaN值替换为0

# 统计每个商品被评分的次数
df3 = df_behavior[df_behavior['action'] == 'rate'].groupby('item_id').size().reset_index()
df3.columns = ['item_id', 'rate_count'] # 将列名改为rate_count
df['rate_count'] = df3['rate_count'] # 将df3的rate_count列赋给df的rate_count列
df['rate_count'] = df['rate_count'].fillna(0) # 将df的rate_count列中的NaN值替换为0

# 统计每个商品的评分合计
df4 = df_behavior[df_behavior['action'] == 'rate'].groupby('item_id')['rating'].sum().reset_index()
df4.columns = ['item_id', 'rate_sum'] # 将列名改为rate_sum
df['rate_sum'] = df4['rate_sum'] # 将df4的rate_sum列赋给df的rate_sum列
df['rate_sum'] = df['rate_sum'].fillna(0) # 将df的rate_sum列中的NaN值替换为0

# 计算每个商品的评分平均值, 分母不为0
df['rate_avg'] = df['rate_sum'] / df['rate_count'].replace(0, 1) # 将df的rate_sum列除以df的rate_count列, 如果df的rate_count列为0, 则替换为1
df['rate_avg'] = df['rate_avg'].fillna(0) # 将df的rate_avg列中的NaN值替换为0

# 删除rate_count列
df = df.drop('rate_count', axis=1)

# 计算每个商品的销售额
df['revenue'] = df['sales'] * df['price'] # 将df的sales列和price列相乘, 得到销售额

# 将df保存为csv文件
df.to_csv('item_data.csv', index=False)